#encoding: utf-8
load 'rds_api.rb'
load 'tutem_utils.rb'

@rds = @rds_tt

# name:   service_utils.rb
# usage:  客服相关操作库
# 1) updateCS 用于将尚未设置客服账号关联的用户，根据最新的客服备注进行关联
# 2) cs_aid = 0；初始指，默认未0
# 3）cs_aid = -1；有成交订单或者备注，但尚未明确客服
# 4）cs_aid > 0；有指定默认客服

#搜索所有未设置客服代表的用户
def updateUserServiceRelationship

    #重置所有cs_aid标记
    #queryRds("update tutem_mall.eb_user set cs_aid = 0")

    #所有新增加备注但仍无客服的用户备注为-1
    queryRds("update tutem_mall.eb_user set cs_aid = -1 where mark != '' and cs_aid = 0")

    #将在mark中客服有备注，但没有设置cs_aid的用户，根据备注中的客服昵称进行登记
    service_nicks = {  
        20 => ['小图','华华','妍妍'], 
        21 => ['夏夏'],
        24 => ['媛媛', '李婧媛']
    }
    cs_aids = service_nicks.keys

    first_time = true
    likes = ''
    service_nicks.each do |aid, nicks|
        nicks.each do |nick|
            likes += " or" if !first_time
            likes += " mark like '%#{nick}%'"
            first_time = false
        end
    end
    
    likes = " and ( #{likes} )" if !likes.empty?
    sql = "select uid, mark from tutem_mall.eb_user where cs_aid <= 0 #{likes}"
    usqls = []
    marked = 0
    (queryRds(sql)).each do |q|

        marked += 1

        uid = q['uid']
        mark = q['mark']

        cs_aid = 0
        cs_aids.each do |aid|
            nicks = service_nicks[aid]
            nicks.each do |nick|
                cs_aid = aid if mark.include? nick
                break if mark.include? nick         #只要有一个match上就可以跳过其它比较了
            end
        end
        usql = "update tutem_mall.eb_user set cs_aid = #{cs_aid} where uid = #{uid} and cs_aid <= 0"
        usqls += [usql]

    end
    commitTrans usqls

    #将所有未设置cs_aid的用户，如果有用户档案，取最新明细查找纪录者的平台
    sql = " UPDATE tutem_mall.eb_user u INNER JOIN (
            SELECT uid, aid, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY id DESC) AS rn FROM tutem_mall.eb_user_profile WHERE aid != 2
        ) p ON p.uid = u.uid AND p.rn = 1
        SET cs_aid = p.aid where u.cs_aid <= 0;"
    queryRds(sql)

    #对所有cs_aid仍为零但已经有订单的用户重置cs_aid==-1以便寻找方便
    sql = "update tutem_mall.eb_user set cs_aid = -1 where cs_aid <= 0 and uid in (select uid from tutem_mall.eb_store_order where paid = 1);"
    queryRds sql

end
